package com.qtummatrix.dao.impl;

import com.qtummatrix.dao.ModelDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;
/**
 * create by Gongshiyong  2019-11-21 19:39
 */

@Repository
public class ModelDaoImpl implements ModelDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Map<String, Object>> getAllproductBankName() {
        String sql = "select id, bankname from BankinfoEntity";
        List<Map<String, Object>> allBankName = jdbcTemplate.queryForList(sql);
        return allBankName;
    }

    public List<Map<String, Object>> getAllproductSerie() {
        String sql = "select id, serieName from SeriesEntity";
        List<Map<String, Object>> allSerie = jdbcTemplate.queryForList(sql);
        return allSerie;
    }

    public List<Map<String, Object>> getAllproductName() {
        String sql = "select id, loanname from loanproductsentity";
        List<Map<String, Object>> allProductName = jdbcTemplate.queryForList(sql);
        return allProductName;
    }

    public List<Map<String, Object>> getAllcustomType() {
        String sql = "select id, typename from customtypeentity";
        List<Map<String, Object>> allCustomType = jdbcTemplate.queryForList(sql);
        return allCustomType;
    }

    public List<Map<String, Object>> getAllmodelState() {
        String sql = "select id, productState from StateEntity";
        List<Map<String, Object>> allState = jdbcTemplate.queryForList(sql);
        return allState;
    }

    public List<Map<String, Object>> getModelList(int bankid, String modelName, int seriesid, int product, int customType, int state, int pageNo, int pageSize) {
        String sql = "select m.id, m.modelName, b.bankname, m.division, series.serieName, p.loanName, c.typeName, m.percentageDown, m.scoreDown, m.loanType,m.area,state.productState from modelentity m left join bankinfoentity b on m.bankid=b.id left join seriesentity series on m.seriesid=series.id left join loanproductsentity p on m.product=p.id left join customtypeentity c on m.customType=c.id left join stateentity state on m.state=state.id where 1=1 ";
        if(bankid != 0){
            sql+="and m.bankid=? ";
        }else{
            sql+="and (1=1 or m.bankid=?) ";
        }
        if(!modelName.equals("")){
            sql+="and m.modelName=? ";
        }else{
            sql+="and (1=1 or m.modelName=?) ";
        }
        if(seriesid != 0){
            sql+="and m.seriesid=? ";
        }else{
            sql+="and (1=1 or m.seriesid=?) ";
        }
        if(product != 0){
            sql+="and m.product=? ";
        }else{
            sql+="and (1=1 or m.product=?) ";
        }
        if(customType != 0){
            sql+="and m.customType=? ";
        }else{
            sql+="and (1=1 or m.customType=?) ";
        }
        if(state != 0){
            sql+="and m.state=? ";
        }else{
            sql+="and (1=1 or m.state=?) ";
        }

        sql+="order by m.id limit "+(pageNo * pageSize) + ", "+pageSize;
        List<Map<String, Object>> modelList = jdbcTemplate.queryForList(sql,bankid,modelName,seriesid,product,customType,state);
        return modelList;
    }

    public Integer getModelSize(int bankid, String modelName, int seriesid, int product, int customType, int state) {
        String sql = "select count(*) from modelentity where 1=1 ";
        if(bankid != 0){
            sql+="and bankid=? ";
        }else{
            sql+="and (1=1 or bankid=?) ";
        }
        if(!modelName.equals("")){
            sql+="and modelName=? ";
        }else{
            sql+="and (1=1 or modelName=?) ";
        }
        if(seriesid != 0){
            sql+="and seriesid=? ";
        }else{
            sql+="and (1=1 or seriesid=?) ";
        }
        if(product != 0){
            sql+="and product=? ";
        }else{
            sql+="and (1=1 or product=?) ";
        }
        if(customType != 0){
            sql+="and customType=? ";
        }else{
            sql+="and (1=1 or customType=?) ";
        }
        if(state != 0){
            sql+="and state=? ";
        }else{
            sql+="and (1=1 or state=?) ";
        }
        Integer modelSize = jdbcTemplate.queryForObject(sql,Integer.class,bankid,modelName,seriesid,product,customType,state);
        return modelSize;
    }

    public int updateModelState(int id, int state) {
        String sql = "update modelentity set state = ? where id = ?";
        int count = jdbcTemplate.update(sql,state,id);
        return count;
    }

    @Override
    public int addModel(String modelName, int bankid, String division, int seriesid, int product, int customType, BigDecimal percentageDown, int scoreDown, String loanType, String area) {
        String sql = "insert into modelentity values(null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 2)";
        int count = jdbcTemplate.update(sql,modelName,bankid,division,seriesid,product,customType,percentageDown,scoreDown,loanType,area);
        return count;
    }

    @Override
    public List<Map<String, Object>> getModelinfo(int id) {
        String sql = "select m.id, m.modelName, b.bankname, m.division, series.serieName, p.loanName, c.typeName, m.percentageDown, m.scoreDown, m.loanType,m.area,state.productState from modelentity m left join bankinfoentity b on m.bankid=b.id left join seriesentity series on m.seriesid=series.id left join loanproductsentity p on m.product=p.id left join customtypeentity c on m.customType=c.id left join stateentity state on m.state=state.id where m.id="+id;
        List<Map<String, Object>> modelList = jdbcTemplate.queryForList(sql);
        return  modelList;
    }

    @Override
    public int updateModelinfo(int id, String modelName, String division, BigDecimal percentageDown, int scoreDown, String loanType, String area) {
        String sql = "update modelentity set modelName = ? , division = ? , percentageDown = ? , scoreDown = ? , loanType = ? , area = ? where id = ?";
        int count = jdbcTemplate.update(sql,modelName,division,percentageDown,scoreDown,loanType,area,id);
        return count;
    }

    @Override
    public List<Map<String, Object>> getQuestionList(int id, int pageNo, int pageSize) {
        String sql = "select q.id, q.content, q.answer, qk.questname, q.A, q.B, q.C, q.D from modelentity m1 inner join model_moudle mm on m1.id = mm.modelid inner join moudleentity m2 on mm.moudleid = m2.id inner join moudle_question mq on m2.id = mq.moudleId inner join questionsentity q on mq.issueId = q.id inner join questkindentity qk on q.kindid = qk.id where m1.id = ? ";
        sql+="order by q.id limit "+(pageNo * pageSize) + ", "+pageSize;
        List<Map<String, Object>> modelList = jdbcTemplate.queryForList(sql,id);
        return modelList;
    }

    @Override
    public Integer getQuestionSize(int id) {
        String sql = "select count(*) from modelentity m1 inner join model_moudle mm on m1.id = mm.modelid inner join moudleentity m2 on mm.moudleid = m2.id inner join moudle_question mq on m2.id = mq.moudleId inner join questionsentity q on mq.issueId = q.id where m1.id = ?";
        Integer questionSize = jdbcTemplate.queryForObject(sql,Integer.class,id);
        return questionSize;
    }
}
